package zuoye.Demo2.dao;
import zuoye.Demo2.entity.Subject;
import zuoye.Demo2.util.DBUtil;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class SubjectDao {
    //插入学科信息并返回主键
    public int add(Subject s){
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            String sql ="insert into s_subject (subjectname,classhour,gradeid) values (?,?,?)";
            ps = conn.prepareStatement(sql,com.mysql.jdbc.Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,s.getSubjectname());
            ps.setInt(2,s.getClasshour());
            ps.setLong(3, s.getGradeid());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()){
                int id =rs.getInt(1);
                return id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return -1;
    }

    //修改
    public int update(Subject s)
    {
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="update s_subject set subjectname=?,classhour=?,gradeid=? where id=?";
            ps =conn.prepareStatement(sql);
            ps.setString(1,s.getSubjectname());
            ps.setInt(2,s.getClasshour());
            ps.setLong(3,s.getGradeid());
            ps.setLong(4,s.getId());
            int num = ps.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //删除
    public int del(long id){
        Connection conn =null;
        PreparedStatement ps=null;
        try {
            conn =DBUtil.getConnection();
            String sql ="delete from s_subject where id=?";
            ps =conn.prepareStatement(sql);
            ps.setLong(1,id);
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //查询全部课程信息
    public List<Subject> findALL(){
        List<Subject>datas=new LinkedList<>();
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement("select * from s_subject order by id desc");
            rs=ps.executeQuery();
            while (rs.next()){
                Subject su=new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));
                datas.add(su);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return datas;
    }
    //根据主键找单个对象的查询方法
    public Subject get(long id){
        Connection connection =DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=connection.prepareStatement("select * from s_subject where id=?");
            ps.setLong(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                Subject su=new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));
                return su;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return null;
    }
}
